clear all
                                                                                                          
set more off     

set memory 10G
set matsize 10000
set maxvar  20000
set scrollbufsize 500000

cd "F:\TDriveCopy\Research\Nikhil\CreditLimit\Revision\Data\"

**** Dataset created in Revision/Code/getSTWFVars.sas ****
use Y9CDataForBHCs, clear

drop if missing(rssd_id)==1

bys rssd_id date: drop if _n>1
*** 0 obs dropped **

rename rssd_id bank_id

// Dropping unneccesary variables.
drop bhcb* bhck* rssd9* bhdm* bhod* bhfn* cert location zipcode state insured charter reg_high_hold 

/*** 
	Note: In an intermediate step, which has been omitted from this code due to confidentiality reasons, Y9CDataForBHCs.dta was merged 
	with the bank names from our credit bureau data. We focus on only credit card-issuing banks. Please see the data section of the paper for more details. 
	Subsequently, in the merged dataset, banks are ranked based on asset size. This bank rank is denoted by the variable "rank" which is utilized in the code below. 
	The variable "rank" identifies the banks in our sample uniquely.  
***/


tempfile temp1
save `"`temp1'"', replace

duplicates drop rank date, force // 0 dups.

sort rank date


// START:One bank in our sample did not file Y9C data before 20090630. So we use aggregated Call data for that bank and append the aggregated dataset.
preserve 

// AggregatedCallDataToBHCLvl constructed in AggregateCallData.do
use AggregatedCallDataToBHCLvl, clear
duplicates drop rank date, force // 0 dups.

keep if rank==XXX // Note: Exact bank rank masked because of confidentiality reasons
keep if date<20090630

// Notes for Call data variable construction: deposits variable just includes domestic deposits. 
egen tot_deposits = rowtotal(deposits foreigndep), missing

rename tot_loans_gross tot_loans_leases
rename foreigndep foreign_deposits
rename timedepge100k large_deposits
rename fedfundspur fedf_purch_only
rename repos repo

local remvar cp loans_late_90d_incl_sec loans_not_accr_incl_sec
local sumvarslist: list sumvarslist - remvar

keep rank ultimaterssd_id date year month bankname bank_id `sumvarslist'
save `"`temp1'"', replace

restore
append using `"`temp1'"'
// END

duplicates drop rank date, force // 0 dups.
sort rank date


// START: BROKERED DEPOSITS HAVE TO BE AGGREGATED FROM CALL REPORTS AS THEY ARE NOT REPORTED AT BHC LEVEL. 
// UNUSED CC LIMITS WERE MISSING FOR SOME YEARS FOR SOME BANKS, SO GETTING THIS INFORMAITON FROM CALL REPORTS TOO.
preserve 
// AggregatedCallDataToBHCLvl constructed in AggregateCallData.do
use AggregatedCallDataToBHCLvl, clear
duplicates drop rank date, force // 0 dups.

egen brokereddep_lt1yr = rowtotal(brokereddeplt100k_lt1yr brokereddepge100k_lt1yr), missing
format brokereddep brokereddep_lt1yr %12.0g

rename unused_cc_commit unused_cc_commit_calldata

keep rank date brokereddep brokereddep_lt1yr brokereddeplt100k brokereddepge100k brokereddeplt100k_lt1yr brokereddepge100k_lt1yr unused_cc_commit_calldata

save `"`temp1'"', replace

restore

merge 1:1 rank date using `"`temp1'"'
drop if _merge==2
drop _merge

keep if year>=2002 & year<=2017
// END: GET BROKERED DEPOSITS & UNUSED CC LIMITS

gen temp = mdy(month,1,year)
format temp %td
gen dateq = qofd(temp)
format dateq %tq
drop temp

xtset rank dateq, quarterly

// Assets deposits loans are nonmissing for all obs in dataset
bys rank (dateq): gen g_assets = (tot_assets/L1.tot_assets) - 1
gen deposits_assets = tot_deposits/tot_assets
gen loans_assets = tot_loans_leases/tot_assets
replace unused_cc_commit = unused_cc_commit_calldata if missing(unused_cc_commit)==1 //unused CC missing for year=2010. Checked that aggregated call report values are very similar to BHC values for most big banks, so replacing.


egen wholesale_funding_st = rowtotal(timedepge100k_lt1yr foreigntimedep_lt1yr brokereddeplt100k_lt1yr fedfundsrepoliab otherborrowedmoney_lt1yr), missing
egen wholesale_funding_st_tradliab = rowtotal(wholesale_funding_st tradingliab_exclreval), missing

egen wholesale_deposits = rowtotal(brokereddeplt100k foreign_deposits large_deposits), missing

egen wholesale_funding_total = rowtotal(wholesale_deposits fedfundsrepoliab otherborrowedmoney_lt1yr otherborrowedmoney_gt1yr), missing

gen neg_wholesale_deposits = -wholesale_deposits
egen stable_funding = rowtotal(tot_deposits neg_wholesale_deposits), missing
drop neg_wholesale_deposits


egen STunstable_dep = rowtotal(timedepge100k_lt1yr foreigntimedep_lt1yr brokereddeplt100k_lt1yr)
egen STnondeposit_funds = rowtotal(fedfundsrepoliab otherborrowedmoney_lt1yr)


gen STW_funding_liab = wholesale_funding_st/tot_liabilities // EXPOSURE measure (main independent variable)
gen STWTrd_funding_liab = wholesale_funding_st_tradliab/tot_liabilities
gen wholesale_deposits_liab = wholesale_deposits/tot_liabilities
gen stable_funding_liab = stable_funding/tot_liabilities
gen TotWholesaleFunding_liab = wholesale_funding_total/tot_liabilities
gen STW_funding_assets = wholesale_funding_st/tot_assets


gen tot_liabilities_assets = tot_liabilities/tot_assets
gen equity_cap_assets = equity_capital/tot_assets
gen TotWholesaleFunding_assets = wholesale_funding_total/tot_assets


gen STunstable_dep_liab = STunstable_dep/tot_liabilities
gen STnondeposit_funds_liab = STnondeposit_funds/tot_liabilities
gen STforeigndep_liab = foreigntimedep_lt1yr/tot_liabilities
gen STlargedep_liab = timedepge100k_lt1yr/tot_liabilities
gen STbrokereddep_liab = brokereddeplt100k_lt1yr/tot_liabilities
gen STrepo_liab = repo/tot_liabilities
gen STfedfpurch_liab = fedf_purch_only/tot_liabilities
gen STobmlt1yr_liab = otherborrowedmoney_lt1yr/tot_liabilities
gen STTradingliab_liab = tradingliab_exclreval/tot_liabilities
gen obmgt1yr_liab = otherborrowedmoney_gt1yr/tot_liabilities

keep if year>=2002 & year<=2014

// GENERATING OTHER CONTROLS
gen log_assets = log(tot_assets)
gen log_assets_sq = log_assets*log_assets

gen tot_risk_based_capratio_der = (tot_risk_based_cap/net_risk_wt_assets)*100
gen roe =  net_income/equity_capital
gen nonperfloans_assets = nonperf_loans/tot_assets
gen cc_loans_assets = tot_cc_loans/tot_assets
gen mortgage_loans_assets = mortgage_loans/tot_assets

egen Loanlossreserves = rowtotal(alw_loan_leases_losses alloc_trans_risk_reserves), missing
gen LLR_TotalLoans = Loanlossreserves/tot_loans_leases
gen LLR_assets = Loanlossreserves/tot_assets
gen LLProvision_TotalLoans = provision_loans_losses/tot_loans_leases
gen LLProvision_assets = provision_loans_losses/tot_assets

gen unusedCC_CCloans = unused_cc_commit/tot_cc_loans
gen CCsoldsecuritized_CCloans = cc_sold_sec/tot_cc_loans
  
local winvarlist STW_funding_assets STWTrd_funding_assets STW_funding_stableDeps STWTrd_funding_stableDeps STWF_Deposits_old log_assets log_assets_sq tot_risk_based_capratio_der roe nonperfloans_assets Loanlossreserves LLR_TotalLoans LLR_assets LLProvision_TotalLoans LLProvision_assets mortgage_loans_assets cc_loans_assets unusedCC_CCloans CCsoldsecuritized_CCloans STunstable_dep_assets STnondeposit_funds_assets cp_assets STWF_old_assets STforeigndep_assets STlargedep_assets STbrokereddep_assets tot_liabilities_assets *_liab

unab winvarlist : `winvarlist'

foreach winvar of local winvarlist {
	winsor `winvar', gen(w`winvar') p(0.01) 
	replace `winvar' = w`winvar'
	drop w`winvar'
}

save processedBankVariables, replace

